package DataAccess;

import DomainModel.*;
import java.sql.*;
import java.util.Calendar;
import java.util.LinkedList;
import java.util.List;
import java.util.UUID;
import org.jasypt.util.password.BasicPasswordEncryptor;

/**
 *
 * @author Team2
 *
 */
public class UserDA {

    private Connection conn;
    private PreparedStatement prep;

    public UserDA() throws Exception {
        conn = DBManager.getInstance().getConnection();
    }

    public User loginUser(String uname, String pwd) throws Exception {
        User user = null;

        String q = "SELECT * FROM users WHERE user_name = ?";
        prep = conn.prepareStatement(q);
        prep.setString(1, uname);

        ResultSet rs = prep.executeQuery();

        try {
            if (rs.next()) { //if the user was found
                //encrypt password
                BasicPasswordEncryptor passwordEncryptor = new BasicPasswordEncryptor();
                String encryptedPassword = rs.getString("password");
                boolean passwordCorrect = passwordEncryptor.checkPassword(pwd, encryptedPassword);

                if (uname.trim().equals(rs.getString("user_name")) && passwordCorrect) {
                    user = (this.createUser(rs));
                    System.out.println("User login authenticated");
                }
            }
        } catch (Exception e) {
            System.out.println("validateLogin: Error while validating password: " + e.getMessage());
            throw e;
        } finally {
            prep.close();
        }

        return user;
    }

    public List<User> getAllUsers() throws Exception {
        List<User> allUsers = new LinkedList<>();

        String q = "SELECT * FROM users ";
        prep = conn.prepareStatement(q);

        try (ResultSet rs = prep.executeQuery()) {
            while (rs.next()) {
                allUsers.add(this.createUser(rs));
            }
        } catch (Exception e) {
            System.out.println("validateLogin: Error while validating password: " + e.getMessage());
            throw e;
        } finally {
            prep.close();
        }

        return allUsers;
    }

    public List<User> getAllProfessors() throws Exception {
        List<User> allProfs = new LinkedList<>();

        String q = "SELECT * FROM users WHERE role_id = 2";
        prep = conn.prepareStatement(q);

        try (ResultSet rs = prep.executeQuery()) {
            while (rs.next()) {
                allProfs.add(this.createUser(rs));
            }
        } catch (Exception e) {
            System.out.println("Error while fetching professors list: " + e.getMessage());
            throw e;
        } finally {
            prep.close();
        }

        return allProfs;

    }

    public void setUser(User user, String password) throws SQLException {
        String q;

        if (user.getUserId() == 0) {
            //encrypt password
            BasicPasswordEncryptor passwordEncryptor = new BasicPasswordEncryptor();
            String encryptedPassword = passwordEncryptor.encryptPassword(password);

            // INSERT new user
            q = "INSERT INTO users (user_name, password, first_name, last_name, email, role_id) "
                    + "VALUES (?, ?, ?, ?, ?, ?)";
            prep = conn.prepareStatement(q);
            prep.setString(1, user.getUsername());
            prep.setString(2, encryptedPassword);
            prep.setString(3, user.getFirstName());
            prep.setString(4, user.getLastName());
            prep.setString(5, user.getEmail());
            prep.setInt(6, user.getRole().getRoleId());
        } else {
            // UPDATE user
            q = "UPDATE users "
                    + "SET user_name = ?, first_name =?, last_name = ?, email = ?, role_id = ? "
                    + "WHERE id = ?";
            prep = conn.prepareStatement(q);
            prep.setString(1, user.getUsername());
            prep.setString(2, user.getFirstName());
            prep.setString(3, user.getLastName());
            prep.setString(4, user.getEmail());
            prep.setInt(5, user.getRole().getRoleId());
            prep.setInt(6, user.getUserId());
        }

        try {
            prep.executeUpdate();
        } catch (SQLException e) {
            throw e;
        } finally {
            prep.close();
        }
    }

    public void changeUserPassword(int userId, String password) throws SQLException {

        //encrypt password
        BasicPasswordEncryptor passwordEncryptor = new BasicPasswordEncryptor();
        String encryptedPassword = passwordEncryptor.encryptPassword(password);

        String q;

        // UPDATE user
        q = "UPDATE users SET password = ? WHERE id = ?";
        prep = conn.prepareStatement(q);
        prep.setString(1, encryptedPassword);
        prep.setInt(2, userId);

        try {
            prep.executeUpdate();
        } catch (SQLException e) {
            throw e;
        } finally {
            prep.close();
        }
    }

    private User createUser(ResultSet rs) throws Exception {
        if (rs == null) {
            return null;
        }

        int user_id = rs.getInt("id");
        String username = rs.getString("user_name");
        String firstname = rs.getString("first_name");
        String lastname = rs.getString("last_name");
        String email = rs.getString("email");
        int role_id = rs.getInt("role_id");
        Role pRole = Role.getRoleById(role_id);

        return new User(user_id, username, firstname, lastname, email, pRole);
    }

    User getUserById(int user_id) throws Exception {
        User user = null;
        String q = "SELECT * FROM users WHERE id = ?";
        prep = conn.prepareStatement(q);
        prep.setInt(1, user_id);

        ResultSet rs = prep.executeQuery();

        try {
            if (rs.next()) { //if the user was found
                if (user_id == rs.getInt("id")) {
                    user = (this.createUser(rs));
                }
            }
        } catch (Exception e) {
            System.out.println("getUserById: Error while creating user by id: " + e.getMessage());
            throw e;
        } finally {
            prep.close();
        }
        return user;
    }

    User getUserByEmail(String user_email) throws Exception {
        User user = null;
        String q = "SELECT * FROM users WHERE email = ?";
        prep = conn.prepareStatement(q);
        prep.setString(1, user_email);

        ResultSet rs = prep.executeQuery();

        try {
            if (rs.next()) { //if the user was found
                if (user_email.equalsIgnoreCase(rs.getString("email"))) {
                    user = (this.createUser(rs));
                }
            }
        } catch (Exception e) {
            System.out.println("getUserByEmail: Error while creating user by email: " + e.getMessage());
            throw e;
        } finally {
            prep.close();
        }
        return user;
    }

    List<User> getAllStudents() throws Exception {
        List<User> allStuds = new LinkedList<>();

        String q = "SELECT * FROM users WHERE role_id = 1";
        prep = conn.prepareStatement(q);

        try (ResultSet rs = prep.executeQuery()) {
            while (rs.next()) {
                allStuds.add(this.createUser(rs));
            }
        } catch (Exception e) {
            System.out.println("Error while fetching students list: " + e.getMessage());
            throw e;
        } finally {
            prep.close();
        }

        return allStuds;
    }

    boolean studentEnrolledInSection(int userId, int sectionId) throws Exception {
        boolean enrolledStatus = false;
        String q = "select * from students_sections "
                + "where student_id = ? and section_id = ?";
        prep = conn.prepareStatement(q);
        prep.setInt(1, userId);
        prep.setInt(2, sectionId);
        try (ResultSet rs = prep.executeQuery()) {
            if (rs.next()) {
                enrolledStatus = true;
            }
        } catch (Exception e) {
            System.out.println("Error while fetching students list: " + e.getMessage());
            throw e;
        } finally {
            prep.close();
        }

        return enrolledStatus;
    }
}
